{
 "metadata": {
  "name": "",
  "signature": "sha256:e9a51db68a345ff7726ae5b55a45c5d5fc5b6fd478b5fb7cfdc0df20b9592dbe"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "import sqlite3"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# 9.1 Reading data from SQL databases"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "So far we've only talked about reading data from CSV files. That's a pretty common way to store data, but there are many others! Pandas can read from HTML, JSON, SQL, Excel (!!!), HDF5, Stata, and a few other things. In this chapter we'll talk about reading data from SQL databases.\n",
      "\n",
      "You can read data from a SQL database using the `pd.read_sql` function. `read_sql` will automatically convert SQL column names to DataFrame column names.\n",
      "\n",
      "`read_sql` takes 2 arguments: a `SELECT` statement, and a database connection object. This is great because it means you can read from *any* kind of SQL database -- it doesn't matter if it's MySQL, SQLite, PostgreSQL, or something else.\n",
      "\n",
      "This example reads from a SQLite database, but any other database would work the same way."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Let's look at the database file\n",
      "! head ../data/weather_2012.sqlite"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "SQLite format 3\u0000\u0004\u0000\u0001\u0001\u0000@  \u0000\u0000\u0000\u0004\u0000\u0000\u0000\u0007\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0003\u0000\u0000\u0000\u0004\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0001\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0004\u0000-\ufffd%\r",
        "\u0000\u0000\u0000\u0002\u0002\ufffd\u0000\u0003>\u0002\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000P\u0002\u0006\u0017++\u0001Ytablesqlite_sequencesqlite_sequence\u0003CREATE TABLE sqlite_sequence(name,seq)\ufffd?\u0001\u0007\u0017%%\u0001\ufffdAtableweather_2012weather_2012\u0002CREATE TABLE weather_2012 (\r\n",
        "            id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n",
        "            date_time TIMESTAMP,\r\n",
        "            temp DOUBLE PRECISION\r\n",
        "     "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "   )\u0005\u0000\u0000\u0000\u0003\u0003\ufffd\u0000\u0000\u0000\u0000\u0007\u0003\ufffd\u0003\ufffd\u0003\ufffd\u0003:\u0003\u0019\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002{\u0002Z\u00029\u0002\u0018\u0001\ufffd\u0001\ufffd\u0001\ufffd\u0001\ufffd\u0001z\u0001Y\u0001?\u0001\u001e",
        "\u0000\ufffd\u0000\ufffd\u0000\ufffd\u0000\ufffd\u0000y\u0000X\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u001f\u001d",
        "\u0004\u00003\u00072012-01-02 04:00:00@\u0007333333\u001f\u001c",
        "\u0004\u00003\u00072012-01-02 03:00:00@\r",
        "\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u001b\u0004\u00003\u00072012-01-02 02:00:00@\u000f333333\u001f\u001a\u0004\u00003\u00072012-01-02 01:00:00@\u0012ffffff\u001f\u0019\u0004\u00003\u00072012-01-02 00:00:00@\u0014\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0018\u0004\u00003\u00072012-01-01 23:00:00@\u0015333333\u001f\u0017\u0004\u00003\u00072012-01-01 22:00:00@\u0011\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u0018\u0016\u0004\u00003\u00012012-01-01 21:00:00\u0004\u001f\u0015\u0004\u00003\u00072012-01-01 20:00:00@\t\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0014\u0004\u00003\u00072012-01-01 19:00:00@\b\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0013\u0004\u00003\u00072012-01-01 18:00:00@\u000effffff\u0018\u0012\u0004\u00003\u00012012-01-01 17:00:00\u0003\u001f\u0011\u0004\u00003\u00072012-01-01 16:00:00@\u0004\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0010\u0004\u00003\u00072012-01-01 15:00:00?\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u000f\u0004\u00003\u00072012-01-01 14:00:00?\u9659\ufffd\ufffd\ufffd\ufffd\u001f\u000e\u0004\u00003\u00072012-01-01 13:00:00?\u0259\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\r",
        "\u0004\u00003\u00072012-01-01 12:00:00\ufffd\u0259\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\f",
        "\u0004\u00003\u00072012-01-01 11:00:00\ufffd\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u0018\u000b",
        "\u0004\u00003\u00012012-01-01 10:00:00\ufffd\u001f\r\n",
        "\u0004\u00003\u00072012-01-01 09:00:00\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\t\u0004\u00003\u00072012-01-01 08:00:00\ufffd\ufffdffffff\u001f\b\u0004\u00003\u00072012-01-01 07:00:00\ufffd\ufffdffffff\u001f\u0007\u0004\u00003\u00072012-01-01 06:00:00\ufffd\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u001f\u0006\u0004\u00003\u00072012-01-01 05:00:00\ufffd\ufffdffffff\u001f\u0005\u0004\u00003\u00072012-01-01 04:00:00\ufffd\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u001f\u0004\u0004\u00003\u00072012-01-01 03:00:00\ufffd\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u001f\u0003\u0004\u00003\u00072012-01-01 02:00:00\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0002\u0004\u00003\u00072012-01-01 01:00:00\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0001\u0004\u00003\u00072012-01-01 0\u0000\u0000\u0000\u0006X\u0000\u0000\u0000\u0005;\u0000\u0000\u0000\u0004\u001d",
        "\r",
        "\u0000\u0000\u0000\u0001\u0003\ufffd\u0000\u0003\ufffd\u0000\u0002\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0010\u0001\u0003%\u0001weather_2012d\r",
        "\u0000\u0000\u0000\u001d",
        "\u0000X\u0000\u0003\ufffd\u0003\ufffd\u0003\ufffd\u0003|\u0003[\u0003:\u0003\u0019\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002{\u0002Z\u00029\u0002\u0018\u0001\ufffd\u0001\ufffd\u0001\ufffd\u0001\ufffd\u0001z\u0001Y\u0001?\u0001\u001e",
        "\u0000\ufffd\u0000\ufffd\u0000\ufffd\u0000\ufffd\u0000y\u0000X\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u001f\u001d",
        "\u0004\u00003\u00072012-01-02 04:00:00@\u0007333333\u001f\u001c",
        "\u0004\u00003\u00072012-01-02 03:00:00@\r",
        "\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u001b\u0004\u00003\u00072012-01-02 02:00:00@\u000f333333\u001f\u001a\u0004\u00003\u00072012-01-02 01:00:00@\u0012ffffff\u001f\u0019\u0004\u00003\u00072012-01-02 00:00:00@\u0014\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0018\u0004\u00003\u00072012-01-01 23:00:00@\u0015333333\u001f\u0017\u0004\u00003\u00072012-01-01 22:00:00@\u0011\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u0018\u0016\u0004\u00003\u00012012-01-01 21:00:00\u0004\u001f\u0015\u0004\u00003\u00072012-01-01 20:00:00@\t\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0014\u0004\u00003\u00072012-01-01 19:00:00@\b\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0013\u0004\u00003\u00072012-01-01 18:00:00@\u000effffff\u0018\u0012\u0004\u00003\u00012012-01-01 17:00:00\u0003\u001f\u0011\u0004\u00003\u00072012-01-01 16:00:00@\u0004\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0010\u0004\u00003\u00072012-01-01 15:00:00?\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u000f\u0004\u00003\u00072012-01-01 14:00:00?\u9659\ufffd\ufffd\ufffd\ufffd\u001f\u000e\u0004\u00003\u00072012-01-01 13:00:00?\u0259\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\r",
        "\u0004\u00003\u00072012-01-01 12:00:00\ufffd\u0259\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\f",
        "\u0004\u00003\u00072012-01-01 11:00:00\ufffd\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u0018\u000b",
        "\u0004\u00003\u00012012-01-01 10:00:00\ufffd\u001f\r\n",
        "\u0004\u00003\u00072012-01-01 09:00:00\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\t\u0004\u00003\u00072012-01-01 08:00:00\ufffd\ufffdffffff\u001f\b\u0004\u00003\u00072012-01-01 07:00:00\ufffd\ufffdffffff\u001f\u0007\u0004\u00003\u00072012-01-01 06:00:00\ufffd\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u001f\u0006\u0004\u00003\u00072012-01-01 05:00:00\ufffd\ufffdffffff\u001f\u0005\u0004\u00003\u00072012-01-01 04:00:00\ufffd\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u001f\u0004\u0004\u00003\u00072012-01-01 03:00:00\ufffd\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u001f\u0003\u0004\u00003\u00072012-01-01 02:00:00\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0002\u0004\u00003\u00072012-01-01 01:00:00\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0001\u0004\u00003\u00072012-01-01 00:00:00\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\r",
        "\u0000\u0000\u0000\u001e",
        "\u0000M\u0000\u0003\ufffd\u0003\ufffd\u0003\ufffd\u0003\ufffd\u0003b\u0003A\u0003 \u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002b\u0002A\u0002 \u0001\ufffd\u0001\ufffd\u0001\ufffd\u0001\ufffd\u0001\ufffd\u0001h\u0001G\u0001&\u0001\u0005\u0000\ufffd\u0000\ufffd\u0000\ufffd\u0000\ufffd\u0000n\u0000M\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u001f;\u0004\u00003\u00072012-01-03 10:00:00\ufffd.\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u0018:\u0004\u00003\u00012012-01-03 09:00:00\ufffd\u001f9\u0004\u00003\u00072012-01-03 08:00:00\ufffd-\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u00188\u0004\u00003\u00012012-01-03 07:00:00\ufffd\u001f7\u0004\u00003\u00072012-01-03 06:00:00\ufffd*\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f6\u0004\u00003\u00072012-01-03 05:00:00\ufffd)\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f5\u0004\u00003\u00072012-01-03 04:00:00\ufffd)333333\u001f4\u0004\u00003\u00072012-01-03 03:00:00\ufffd&\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f3\u0004\u00003\u00072012-01-03 02:00:00\ufffd%\u0000\u0000\u0000\u0000\u0000\u0000\u001f2\u0004\u00003\u00072012-01-03 01:00:00\ufffd#ffffff\u00181\u0004\u00003\u00012012-01-03 00:00:00\ufffd\u001f0\u0004\u00003\u00072012-01-02 23:00:00\ufffd\u001d",
        "\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u0018/\u0004\u00003\u00012012-01-02 22:00:00\ufffd\u001f.\u0004\u00003\u00072012-01-02 21:00:00\ufffd\u0017333333\u001f-\u0004\u00003\u00072012-01-02 20:00:00\ufffd\u0016ffffff\u001f,\u0004\u00003\u00072012-01-02 19:00:00\ufffd\u0013333333\u001f+\u0004\u00003\u00072012-01-02 18:00:00\ufffd\u0010ffffff\u001f*\u0004\u00003\u00072012-01-02 17:00:00\ufffd\u0000\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f)\u0004\u00003\u00072012-01-02 16:00:00\ufffd\ufffdffffff\u0017(\u0004\u00003\b2012-01-02 15:00:00\u001f'\u0004\u00003\u00072012-01-02 14:00:00?\ud925\ude59\ufffd\ufffd\ufffd\u001f&\u0004\u00003\u00072012-01-02 13:00:00?\ud925\ude59\ufffd\ufffd\ufffd\u001f%\u0004\u00003\u00072012-01-02 12:00:00?\ufffd333333\u001f$\u0004\u00003\u00072012-01-02 11:00:00@\u0001\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f#\u0004\u00003\u00072012-01-02 10:00:00?\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u001f\"\u0004\u00003\u00072012-01-02 09:00:00?\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f!\u0004\u00003\u00072012-01-02 08:00:00?\ufffdffffff\u0018 \u0004\u00003\u00012012-01-02 07:00:00\u0002\u001f\u001f\u0004\u00003\u00072012-01-02 06:00:00@\u0002ffffff\u001f\u001e",
        "\u0004\u00003\u00072012-01-02 05:00:00@\u0004\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\r",
        "\u0000\u0000\u0000\u001d",
        "\u0000J\u0000\u0003\ufffd\u0003\ufffd\u0003\ufffd\u0003|\u0003[\u0003:\u0003\u0019\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002{\u0002Z\u00029\u0002\u0018\u0001\ufffd\u0001\ufffd\u0001\ufffd\u0001\ufffd\u0001s\u0001R\u00011\u0001\u0010\u0000\ufffd\u0000\ufffd\u0000\ufffd\u0000\ufffd\u0000k\u0000J\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u001fX\u0004\u00003\u00072012-01-04 15:00:00\ufffd$ffffff\u001fW\u0004\u00003\u00072012-01-04 14:00:00\ufffd&\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001fV\u0004\u00003\u00072012-01-04 13:00:00\ufffd(\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001fU\u0004\u00003\u00072012-01-04 12:00:00\ufffd+ffffff\u001fT\u0004\u00003\u00072012-01-04 11:00:00\ufffd,ffffff\u001fS\u0004\u00003\u00072012-01-04 10:00:00\ufffd.ffffff\u001fR\u0004\u00003\u00072012-01-04 09:00:00\ufffd0\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001fQ\u0004\u00003\u00072012-01-04 08:00:00\ufffd1\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001fP\u0004\u00003\u00072012-01-04 07:00:00\ufffd2333333\u001fO\u0004\u00003\u00072012-01-04 06:00:00\ufffd2\ufffd\u0000\u0000\u0000\u0000\u0000\u001fN\u0004\u00003\u00072012-01-04 05:00:00\ufffd2\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001fM\u0004\u00003\u00072012-01-04 04:00:00\ufffd2\ufffd\u0000\u0000\u0000\u0000\u0000\u001fL\u0004\u00003\u00072012-01-04 03:00:00\ufffd2\ufffd\u0000\u0000\u0000\u0000\u0000\u001fK\u0004\u00003\u00072012-01-04 02:00:00\ufffd2\u0019\ufffd\ufffd\ufffd\ufffd\ufffd\u001fJ\u0004\u00003\u00072012-01-04 01:00:00\ufffd1\ufffdfffff\u001fI\u0004\u00003\u00072012-01-04 00:00:00\ufffd1\ufffd\u0000\u0000\u0000\u0000\u0000\u001fH\u0004\u00003\u00072012-01-03 23:00:00\ufffd1L\ufffd\ufffd\ufffd\ufffd\ufffd\u001fG\u0004\u00003\u00072012-01-03 22:00:00\ufffd1\u0019\ufffd\ufffd\ufffd\ufffd\ufffd\u0018F\u0004\u00003\u00012012-01-03 21:00:00\ufffd\u001fE\u0004\u00003\u00072012-01-03 20:00:00\ufffd1L\ufffd\ufffd\ufffd\ufffd\ufffd\u001fD\u0004\u00003\u00072012-01-03 19:00:00\ufffd0\ufffdfffff\u001fC\u0004\u00003\u00072012-01-03 18:00:00\ufffd0L\ufffd\ufffd\ufffd\ufffd\ufffd\u001fB\u0004\u00003\u00072012-01-03 17:00:00\ufffd/\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001fA\u0004\u00003\u00072012-01-03 16:00:00\ufffd.\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f@\u0004\u00003\u00072012-01-03 15:00:00\ufffd-\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f?\u0004\u00003\u00072012-01-03 14:00:00\ufffd-\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f>\u0004\u00003\u00072012-01-03 13:00:00\ufffd.333333\u001f=\u0004\u00003\u00072012-01-03 12:00:00\ufffd-\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f<\u0004\u00003\u00072012-01-03 11:00:00\ufffd-\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\r",
        "\u0000\u0000\u0000\f",
        "\u0002{\u0000\u0003\ufffd\u0003\ufffd\u0003\ufffd\u0003|\u0003[\u0003:\u0003\u0019\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002{\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0018d\u0004\u00003\u00012012-01-05 03:00:00\ufffd\u001fc\u0004\u00003\u00072012-01-05 02:00:00\ufffd\u0015\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001fb\u0004\u00003\u00072012-01-05 01:00:00\ufffd\u001e",
        "\u0000\u0000\u0000\u0000\u0000\u0000\u001fa\u0004\u00003\u00072012-01-05 00:00:00\ufffd!\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f`\u0004\u00003\u00072012-01-04 23:00:00\ufffd#333333\u001f_\u0004\u00003\u00072012-01-04 22:00:00\ufffd#\u0000\u0000\u0000\u0000\u0000\u0000\u001f^\u0004\u00003\u00072012-01-04 21:00:00\ufffd\u001e",
        "ffffff\u001f]\u0004\u00003\u00072012-01-04 20:00:00\ufffd\u001f333333\u001f\\\u0004\u00003\u00072012-01-04 19:00:00\ufffd \ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f[\u0004\u00003\u00072012-01-04 18:00:00\ufffd!\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001fZ\u0004\u00003\u00072012-01-04 17:00:00\ufffd!\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001fY\u0004\u00003\u00072012-01-04 16:00:00\ufffd\"\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd"
       ]
      }
     ],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "con = sqlite3.connect(\"../data/weather_2012.sqlite\")\n",
      "df = pd.read_sql(\"SELECT * from weather_2012 LIMIT 3\", con)\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>id</th>\n",
        "      <th>date_time</th>\n",
        "      <th>temp</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 1</td>\n",
        "      <td> 2012-01-01 00:00:00</td>\n",
        "      <td>-1.8</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 2</td>\n",
        "      <td> 2012-01-01 01:00:00</td>\n",
        "      <td>-1.8</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 3</td>\n",
        "      <td> 2012-01-01 02:00:00</td>\n",
        "      <td>-1.8</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 22,
       "text": [
        "   id            date_time  temp\n",
        "0   1  2012-01-01 00:00:00  -1.8\n",
        "1   2  2012-01-01 01:00:00  -1.8\n",
        "2   3  2012-01-01 02:00:00  -1.8"
       ]
      }
     ],
     "prompt_number": 22
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "`read_sql` doesn't automatically set the primary key (`id`) to be the index of the dataframe. You can make it do that by adding an `index_col` argument to `read_sql`. \n",
      "\n",
      "If you've used `read_csv` a lot, you may have seen that it has an `index_col` argument as well. This one behaves the same way."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = pd.read_sql(\"SELECT * from weather_2012 LIMIT 3\", con, index_col='id')\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>date_time</th>\n",
        "      <th>temp</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>id</th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 2012-01-01 00:00:00</td>\n",
        "      <td>-1.8</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 2012-01-01 01:00:00</td>\n",
        "      <td>-1.8</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> 2012-01-01 02:00:00</td>\n",
        "      <td>-1.8</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 23,
       "text": [
        "              date_time  temp\n",
        "id                           \n",
        "1   2012-01-01 00:00:00  -1.8\n",
        "2   2012-01-01 01:00:00  -1.8\n",
        "3   2012-01-01 02:00:00  -1.8"
       ]
      }
     ],
     "prompt_number": 23
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "If you want your dataframe to be indexed by more than one column, you can give a list of columns to `index_col`:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = pd.read_sql(\"SELECT * from weather_2012 LIMIT 3\", con, \n",
      "                 index_col=['id', 'date_time'])\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th>temp</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>id</th>\n",
        "      <th>date_time</th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <th>2012-01-01 00:00:00</th>\n",
        "      <td>-1.8</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <th>2012-01-01 01:00:00</th>\n",
        "      <td>-1.8</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <th>2012-01-01 02:00:00</th>\n",
        "      <td>-1.8</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 24,
       "text": [
        "                        temp\n",
        "id date_time                \n",
        "1  2012-01-01 00:00:00  -1.8\n",
        "2  2012-01-01 01:00:00  -1.8\n",
        "3  2012-01-01 02:00:00  -1.8"
       ]
      }
     ],
     "prompt_number": 24
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Your turn\n",
      "* How many rows are there in this database?\n",
      "* What is the average temperature?\n",
      "* What are the first and last dates?"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = pd.read_sql(\"SELECT * from weather_2012 \", con, \n",
      "                 index_col=['id', 'date_time'])\n",
      "print df.describe()\n",
      "df.sort().head(), df.sort().tail()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "             temp\n",
        "count  100.000000\n",
        "mean    -6.841000\n",
        "std      7.774487\n",
        "min    -18.600000\n",
        "25%    -14.825000\n",
        "50%     -7.450000\n",
        "75%      0.350000\n",
        "max      5.300000\n"
       ]
      },
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 8,
       "text": [
        "(                        temp\n",
        " id date_time                \n",
        " 1  2012-01-01 00:00:00  -1.8\n",
        " 2  2012-01-01 01:00:00  -1.8\n",
        " 3  2012-01-01 02:00:00  -1.8\n",
        " 4  2012-01-01 03:00:00  -1.5\n",
        " 5  2012-01-01 04:00:00  -1.5,                          temp\n",
        " id  date_time                \n",
        " 96  2012-01-04 23:00:00  -9.6\n",
        " 97  2012-01-05 00:00:00  -8.8\n",
        " 98  2012-01-05 01:00:00  -7.5\n",
        " 99  2012-01-05 02:00:00  -5.4\n",
        " 100 2012-01-05 03:00:00  -5.0)"
       ]
      }
     ],
     "prompt_number": 8
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# 9.2 Writing to a SQLite database"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Pandas has a `write_frame` function which creates a database table from a dataframe. Right now this only works for SQLite databases. Let's use it to move our 2012 weather data into SQL.\n",
      "\n",
      "You'll notice that this function is in `pd.io.sql`. There are a ton of useful functions for reading and writing various kind of data in `pd.io`, and it's worth spending some time exploring them. ([see the documentation!](http://pandas.pydata.org/pandas-docs/stable/io.html))"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "weather_df = pd.read_csv('../data/weather_2012.csv')\n",
      "con = sqlite3.connect(\"../data/test_db.sqlite\")\n",
      "con.execute(\"DROP TABLE IF EXISTS weather_2012\")\n",
      "pd.io.sql.write_frame(weather_df, \"weather_2012\", con)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stderr",
       "text": [
        "/Users/admin/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/io/sql.py:1607: FutureWarning: write_frame is deprecated, use to_sql\n",
        "  warnings.warn(\"write_frame is deprecated, use to_sql\", FutureWarning)\n",
        "/Users/admin/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/io/sql.py:1309: UserWarning: The spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.\n",
        "  warnings.warn(_SAFE_NAMES_WARNING)\n"
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "## OK we follow the advice of the warning and use the new function.\n",
      "con.execute(\"DROP TABLE IF EXISTS weather_2012\")  # don't forget to rop the table first! \n",
      "pd.io.sql.to_sql(weather_df, \"weather_2012\", con)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 15
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "We can now read from the `weather_2012` table in  `test_db.sqlite`, and we see that we get the same data back:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "con = sqlite3.connect(\"../data/test_db.sqlite\")\n",
      "df = pd.read_sql(\"SELECT * from weather_2012 LIMIT 3\", con)\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>index</th>\n",
        "      <th>Date/Time</th>\n",
        "      <th>Temp (C)</th>\n",
        "      <th>Dew Point Temp (C)</th>\n",
        "      <th>Rel Hum (%)</th>\n",
        "      <th>Wind Spd (km/h)</th>\n",
        "      <th>Visibility (km)</th>\n",
        "      <th>Stn Press (kPa)</th>\n",
        "      <th>Weather</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 0</td>\n",
        "      <td> 2012-01-01 00:00:00</td>\n",
        "      <td>-1.8</td>\n",
        "      <td>-3.9</td>\n",
        "      <td> 86</td>\n",
        "      <td> 4</td>\n",
        "      <td> 8</td>\n",
        "      <td> 101.24</td>\n",
        "      <td>                  Fog</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 1</td>\n",
        "      <td> 2012-01-01 01:00:00</td>\n",
        "      <td>-1.8</td>\n",
        "      <td>-3.7</td>\n",
        "      <td> 87</td>\n",
        "      <td> 4</td>\n",
        "      <td> 8</td>\n",
        "      <td> 101.24</td>\n",
        "      <td>                  Fog</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 2</td>\n",
        "      <td> 2012-01-01 02:00:00</td>\n",
        "      <td>-1.8</td>\n",
        "      <td>-3.4</td>\n",
        "      <td> 89</td>\n",
        "      <td> 7</td>\n",
        "      <td> 4</td>\n",
        "      <td> 101.26</td>\n",
        "      <td> Freezing Drizzle,Fog</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 16,
       "text": [
        "   index            Date/Time  Temp (C)  Dew Point Temp (C)  Rel Hum (%)  \\\n",
        "0      0  2012-01-01 00:00:00      -1.8                -3.9           86   \n",
        "1      1  2012-01-01 01:00:00      -1.8                -3.7           87   \n",
        "2      2  2012-01-01 02:00:00      -1.8                -3.4           89   \n",
        "\n",
        "   Wind Spd (km/h)  Visibility (km)  Stn Press (kPa)               Weather  \n",
        "0                4                8           101.24                   Fog  \n",
        "1                4                8           101.24                   Fog  \n",
        "2                7                4           101.26  Freezing Drizzle,Fog  "
       ]
      }
     ],
     "prompt_number": 16
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "The nice thing about having your data in a database is that you can do arbitrary SQL queries. This is cool especially if you're more familiar with SQL. Here's an example of sorting by the Weather column:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "con = sqlite3.connect(\"../data/test_db.sqlite\")\n",
      "df = pd.read_sql(\"SELECT * from weather_2012 ORDER BY Weather LIMIT 3\", con)\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>index</th>\n",
        "      <th>Date/Time</th>\n",
        "      <th>Temp (C)</th>\n",
        "      <th>Dew Point Temp (C)</th>\n",
        "      <th>Rel Hum (%)</th>\n",
        "      <th>Wind Spd (km/h)</th>\n",
        "      <th>Visibility (km)</th>\n",
        "      <th>Stn Press (kPa)</th>\n",
        "      <th>Weather</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td>  67</td>\n",
        "      <td> 2012-01-03 19:00:00</td>\n",
        "      <td>-16.9</td>\n",
        "      <td>-24.8</td>\n",
        "      <td> 50</td>\n",
        "      <td> 24</td>\n",
        "      <td> 25</td>\n",
        "      <td> 101.74</td>\n",
        "      <td> Clear</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 114</td>\n",
        "      <td> 2012-01-05 18:00:00</td>\n",
        "      <td> -7.1</td>\n",
        "      <td>-14.4</td>\n",
        "      <td> 56</td>\n",
        "      <td> 11</td>\n",
        "      <td> 25</td>\n",
        "      <td> 100.71</td>\n",
        "      <td> Clear</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 115</td>\n",
        "      <td> 2012-01-05 19:00:00</td>\n",
        "      <td> -9.2</td>\n",
        "      <td>-15.4</td>\n",
        "      <td> 61</td>\n",
        "      <td>  7</td>\n",
        "      <td> 25</td>\n",
        "      <td> 100.80</td>\n",
        "      <td> Clear</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 26,
       "text": [
        "   index            Date/Time  Temp (C)  Dew Point Temp (C)  Rel Hum (%)  \\\n",
        "0     67  2012-01-03 19:00:00     -16.9               -24.8           50   \n",
        "1    114  2012-01-05 18:00:00      -7.1               -14.4           56   \n",
        "2    115  2012-01-05 19:00:00      -9.2               -15.4           61   \n",
        "\n",
        "   Wind Spd (km/h)  Visibility (km)  Stn Press (kPa) Weather  \n",
        "0               24               25           101.74   Clear  \n",
        "1               11               25           100.71   Clear  \n",
        "2                7               25           100.80   Clear  "
       ]
      }
     ],
     "prompt_number": 26
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Your turn\n",
      "* Use SQL to only put the columns temp and wind_spd in the dataframe and make the date/time the index"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#your code here"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 35
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "If you have a PostgreSQL database or MySQL database, reading from it works exactly the same way as reading from a SQLite database. You create a connection using `psycopg2.connect()` or `MySQLdb.connect()`, and then use\n",
      "\n",
      "`pd.read_sql(\"SELECT whatever from your_table\", con)`"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# 9.3 Connecting to other kinds of database"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "To connect to a MySQL database:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import MySQLdb\n",
      "con = MySQLdb.connect(host=\"localhost\", db=\"test\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "To connect to a PostgreSQL database:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import psycopg2\n",
      "con = psycopg2.connect(host=\"localhost\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "<style>\n",
      "    @font-face {\n",
      "        font-family: \"Computer Modern\";\n",
      "        src: url('http://mirrors.ctan.org/fonts/cm-unicode/fonts/otf/cmunss.otf');\n",
      "    }\n",
      "    div.cell{\n",
      "        width:800px;\n",
      "        margin-left:16% !important;\n",
      "        margin-right:auto;\n",
      "    }\n",
      "    h1 {\n",
      "        font-family: Helvetica, serif;\n",
      "    }\n",
      "    h4{\n",
      "        margin-top:12px;\n",
      "        margin-bottom: 3px;\n",
      "       }\n",
      "    div.text_cell_render{\n",
      "        font-family: Computer Modern, \"Helvetica Neue\", Arial, Helvetica, Geneva, sans-serif;\n",
      "        line-height: 145%;\n",
      "        font-size: 130%;\n",
      "        width:800px;\n",
      "        margin-left:auto;\n",
      "        margin-right:auto;\n",
      "    }\n",
      "    .CodeMirror{\n",
      "            font-family: \"Source Code Pro\", source-code-pro,Consolas, monospace;\n",
      "    }\n",
      "    .text_cell_render h5 {\n",
      "        font-weight: 300;\n",
      "        font-size: 22pt;\n",
      "        color: #4057A1;\n",
      "        font-style: italic;\n",
      "        margin-bottom: .5em;\n",
      "        margin-top: 0.5em;\n",
      "        display: block;\n",
      "    }\n",
      "    \n",
      "    .warning{\n",
      "        color: rgb( 240, 20, 20 )\n",
      "        }  "
     ]
    }
   ],
   "metadata": {}
  }
 ]
}